package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class MyExcel {
	 /**
     * Get excel workbook
     * @param filename
     * @return
     * @throws IOException
     */
    public static HSSFWorkbook readFile(String filename) throws IOException {
        return new HSSFWorkbook(new FileInputStream(filename));
    }


    public static HSSFWorkbook readFile(File file) throws IOException {
        return new HSSFWorkbook(new FileInputStream(file));
    }


    /**
     * Read rows from excel
     * @param inputXLS
     * @param sheetIndex
     * @return
     * @throws Exception
     */
    public static HSSFRow[] readRowsFromExcel(String inputXLS, int sheetIndex) throws Exception {
        //get workbook
        HSSFWorkbook wb = MyExcel.readFile(inputXLS);

        //get sheet
        HSSFSheet sheetx = wb.getSheetAt(sheetIndex);

        //get all rows
        int rowsNum = sheetx.getPhysicalNumberOfRows();
        //check row number
        if(rowsNum<1){
            throw new Exception("Excel "+ inputXLS +" sheet " + sheetIndex + " has no data\n");
        }

        //get rows
        HSSFRow rows[] = new HSSFRow[rowsNum];
        for(int i=0;i<rowsNum;i++){
            rows[i] = sheetx.getRow(i);
        }

        return rows;
    }

    


    /**
     * Get excel value
     * @param cellx
     * @return
     */
    public static String getCellValue(HSSFCell cellx){
        String value = new String();
        if (cellx != null) {
            switch (cellx.getCellType()) {
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cellx.getCellFormula().toString();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = String.format("%f", cellx.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cellx.getStringCellValue();
                    break;

                default:
            }
        }

        return value;
    }

    public static int getLastCellNum(HSSFRow headRow){
    	 return headRow.getLastCellNum();
    }

    public static String[] getColumnNames(HSSFRow headRow){
    	int lastCellIndex = headRow.getLastCellNum();
    	String []colNames = new String[lastCellIndex];
    	for(int i= 0 ;i<lastCellIndex;i++){
    		colNames[i] = MyExcel.getCellValue(headRow.getCell(i));
    	}
    	
    	return colNames;
    }
    
    public static int getColumnNameIndex(String colNames[], String xName){
    	int index = -1;
    	for(int i=0;i<colNames.length;i++){
    		if(xName.equalsIgnoreCase(colNames[i])){
    			index = i;
    			break;
    		}
    	}
    	
    	return index;
    }
    
}
